import xlrd
import xlsxwriter
from getName import get_table_name


class ReadXls(object):
    def __init__(self):
        self.t_data = {}
        self.all_list = []
        self.t_common_header = []
        self.spnum_list = []

    def read_xls_head(self):
        self.t_common_header = [
            ["卷 内 文 件 目 录", "", "", "", "", "", "", ""],
            ["", "", "", "", "", "", "", ""],
            ["全宗号：", "", "", "", "保管期限：", "", "", ""],
            ["", "", "", "", "", "", "", ""],
            ["序号", "档号", "文号", "责任者", "题名", "成文日期", "页号", "备注"],
        ]

        self.t_data["common_header"] = self.t_common_header
        self.all_list.append(self.t_data)
        return self.all_list

    def read_xls_body(self, filepath):
        data = xlrd.open_workbook(filepath)
        names = data.sheet_names()
        self.t_data = {}

        for name in names:
            if name == "卷内":
                sheet_data = []
                table = data.sheet_by_name(name)
                nrows = table.nrows
                ncols = table.ncols

                """取列下标"""
                list_index = ["", "", "", "", "", "", "", ""]
                for ncol in range(ncols):
                    if table.row_values(0)[ncol] == "卷内顺序号":
                        list_index[0] = ncol
                    elif table.row_values(0)[ncol] == "档号":
                        list_index[1] = ncol
                    elif table.row_values(0)[ncol] == "文号":
                        list_index[2] = ncol
                    elif table.row_values(0)[ncol] == "责任者":
                        list_index[3] = ncol
                    elif table.row_values(0)[ncol] == "题名":
                        list_index[4] = ncol
                    elif table.row_values(0)[ncol] == "成文日期":
                        list_index[5] = ncol
                    elif table.row_values(0)[ncol] == "页号":
                        list_index[6] = ncol
                    elif table.row_values(0)[ncol] == "备注":
                        list_index[7] = ncol

                """取数据"""
                for nrow in range(nrows):
                    list_row = []
                    if table.row_values(nrow)[0] == "PARENTID":
                        nrow += 1
                        continue
                    else:
                        for i in list_index:
                            list_row.append(table.row_values(nrow)[i])

                        sheet_data.append(list_row)
                        while len(sheet_data) % 17 != 0:
                            sheet_data.insert(-1, ["", "", "", "", "", "", "", ""])
                        nrow += 1
                        self.spnum_list = list_row[1].split("-")
                        if len(sheet_data) > 1:
                            if self.spnum_list[:-1] != sheet_data[-2][1].split("-")[:-1]:
                                self.t_common_header[2][2] = "".join(self.spnum_list[:1])
                                self.t_common_header[2][6] = "".join(self.spnum_list[2:3])

                                for i in self.t_common_header:
                                    sheet_data.insert(-1, i)

                self.t_data[name] = sheet_data

        self.all_list.append(self.t_data)
        return self.all_list

    def save_to_file(self, result, file_name):
        f = xlsxwriter.Workbook(file_name + "_report.xlsx")
        sheet = f.add_worksheet()

        sheet.set_column("A:A", 3.86)
        sheet.set_column("B:B", 11.29)
        sheet.set_column("C:C", 12.57)
        sheet.set_column("D:D", 10.71)
        sheet.set_column("E:E", 30.57)
        sheet.set_column("F:F", 9.29)
        sheet.set_column("G:G", 6.71)
        sheet.set_column("H:H", 3.71)

        title_format = f.add_format({
            "align": "center",
            "valign": "vcenter",
            "font_name": "宋体",
            "font_size": 24,
            "bold": True,
        })
        second_format = f.add_format({
            "align": "right",
            "valign": "vcenter",
            "bold": True,
        })
        second_content_format = f.add_format({
            "align": "center",
            "valign": "vcenter",
            "bold": True,
        })
        third_format = f.add_format({
            "align": "center",
            "valign": "vcenter",
            "border": 1,
            "bold": True,
        })
        index_format = f.add_format({
            "align": "center",
            "valign": "vcenter",
            "font_size": 12,
            "border": 1,
            "text_wrap": 1
        })

        del result[1]["卷内"][0:21]

        r = 0
        rcnt = 0
        for i in result:
            for j in i.values():
                for k in j:
                    c = 0
                    for l in k:
                        if l == "卷 内 文 件 目 录":
                            rcnt = 0
                            sheet.write(r, c, l)
                            sheet.set_row(r, 31.5)
                            sheet.merge_range(r, c, r, c + 7, l, title_format)

                        elif k[0] == "全宗号：":
                            if l in ["全宗号：", "保管期限："]:
                                sheet.write(r, c, l)
                                sheet.set_row(r, 23)
                                sheet.merge_range(r, c, r, c + 1, l, second_format)
                            else:
                                sheet.write(r, c, l, second_content_format)
                        elif k[0] == "序号":
                            sheet.write(r, c, l, third_format)
                            sheet.set_row(r, 53)

                        if rcnt == 1:
                            sheet.set_row(r, 14.25)
                        elif rcnt == 3:
                            sheet.set_row(r, 11)
                        elif 4 < rcnt < 16:
                            sheet.write(r, c, l, index_format)
                            sheet.set_row(r, 53)
                        elif rcnt == 16:
                            sheet.set_row(r, 8)
                        c += 1
                    rcnt += 1
                    r += 1

        f.close()


if __name__ == "__main__":
    bg = ReadXls()
    bg.read_xls_head()

    for i in get_table_name():
        result = bg.read_xls_body(i)
        bg.save_to_file(result, i)
